package edu.gatech.TNE.wonderLab7000.person;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import edu.gatech.TNE.wonderLab7000.util.WonderLogger;

/**
 * Student class for student methods 
 * 
 * @author Team NamingException
 *
 */
public class student extends person {
	 	
	private Connection conn;
	private ResultSet result;
	private String query;
	private PreparedStatement statement;
	public WonderLogger logger = new WonderLogger("mine");
	public student(String userName, int accessLevel) {
		super(userName, accessLevel);
	}
	
	/**
	 * Registers a student into a course.
	 * 
	 * @param courseName
	 * @return -1 if registration is closed, 0 if student is already registered	in
	 * 			the course, 1 if the student registers successfully into the course
	 * @throws SQLException
	 */
	public int register(String courseName) throws SQLException {
		
		conn = connect();
		query = "SELECT * FROM LABS WHERE COURSE ='" + courseName + "'";
    	statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            conn.close();
            statement.close();
            result.close();
			System.out.println(e.toString());
			logger.error(e.toString());
			
		} 
		
		if (result.next()) {
	        conn.close();
	        statement.close();
	        result.close();
			return -1;			
		}
		
		query = "SELECT * FROM " + courseName + " WHERE STUDENTID ='" + getUserName() + "'";
    	statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            conn.close();
            statement.close();
            result.close();
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		
		if (result.next()) {
	        conn.close();
	        statement.close();
	        result.close();
			return 0;
		}
		
		query = "INSERT INTO " + courseName + " (STUDENT, STUDENTID) VALUES ('" + getName() + "', '" + getUserName() + "')";
	    statement = conn.prepareStatement(query);
	    try {
			result = statement.executeQuery();
		} catch (SQLException e) {
			conn.close();
	        statement.close();
	        result.close();
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
			
		query = "SELECT * FROM COURSES WHERE COURSENAME ='" + courseName + "'";
		statement = conn.prepareStatement(query);
	    try {
	    	result = statement.executeQuery();
		} catch (SQLException e) {
	        conn.close();
	        statement.close();
	        result.close();
			System.out.println(e.toString());
		} 
		result.next();
		int numStudents = result.getInt("NUMSTUDENTS") + 1;
		
		query = "UPDATE COURSES SET NUMSTUDENTS =" + numStudents + " WHERE COURSENAME ='" + courseName + "'";
	    statement = conn.prepareStatement(query);
	    try {
			result = statement.executeQuery();
		} catch (SQLException e) {
	        
	        statement.close();
	        result.close();
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
			
		query = "SELECT * FROM USERS WHERE USERID ='" + getUserName() + "'";
	    statement = conn.prepareStatement(query);
	    try {
	    	result = statement.executeQuery();
		} catch (SQLException e) {
	        conn.close();
	        statement.close();
	        result.close();
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
			
		result.next();
		if (result.getString("COURSES") == null) {
			
			query = "UPDATE USERS SET COURSES ='" + courseName + "' WHERE USERID ='" + getUserName() + "'";
		   	statement = conn.prepareStatement(query);
		    try {
		    	result = statement.executeQuery();
			} catch (SQLException e) {
		        conn.close();
		        statement.close();
		        result.close();
		        System.out.println(e.toString());
		        logger.error(e.toString());
			} 
				
		}
		else {
				
			String courseString = result.getString("COURSES") + "," + courseName;
			query = "UPDATE USERS SET COURSES ='" + courseString + "' WHERE USERID ='" + getUserName() + "'";
		   	statement = conn.prepareStatement(query);
		    try {
				result = statement.executeQuery();
			} catch (SQLException e) {
		        conn.close();
		        statement.close();
		        result.close();
				System.out.println(e.toString());
				logger.error(e.toString());
			} 
				
		}
			
        conn.close();
        statement.close();
        result.close();
		return 1;
	    	
	}	
	
	/**
	 * Creates a team for a given lab.
	 * 
	 * @param labName
	 * @param teamName
	 * @return -1 if max number of teams reached, 0 if team already exists,
	 * 			1 if the team is created
	 * @throws SQLException
	 */
	public int createTeam(String labName, String teamName) throws SQLException {
		
		conn = connect();
		query = "SELECT * FROM " + labName;
    	PreparedStatement statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
			System.out.println("Success");
		} catch (SQLException e) {
            
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		
		int i = 0;
		int teamCount = 0;
		Boolean redunTeam;
		Boolean teamCheck = true;
		String[] teamColl = new String[10000];
		while (result.next()) 
		{
			if(result.getString("TEAM") != null) {
				if (result.getString("TEAM").equals(teamName)) {	
					teamCheck = false;
				}

				redunTeam = false;
				while (teamColl[i] != null) {
					if (result.getString("TEAM").equals((teamColl[i]))) {
						redunTeam = true;					
					}
				
					i++;
				}
			
				if (!redunTeam) {
					teamColl[teamCount] = result.getString("TEAM");
					teamCount++;				
				}
			}
			
		}
		
		query = "SELECT * FROM LABS WHERE LABNAME ='" + labName + "'";
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            conn.close();
            statement.close();
            result.close();
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		
		result.next();
		int teamSize = result.getInt("TEAMSIZE");
		String labCourse = result.getString("COURSE");
		
		query = "SELECT * FROM " + labCourse;
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		
		int studentCount = 0;
		while(result.next()) 
		{
			studentCount++;
		}
		
		if (teamCount == (studentCount/teamSize)) {
            
            result.close();
			return -1;
		}
		
		if (teamCheck) {
			
			query = "UPDATE " + labName + " SET TEAM ='" + teamName + "' WHERE STUDENT ='" + getUserName() + "'";
			statement = conn.prepareStatement(query);
	        try {
				result = statement.executeQuery();
			} catch (SQLException e) {
	            
				System.out.println(e.toString());
				logger.error(e.toString());
			} 		
			
            conn.close();
            statement.close();
            result.close();
			return 1;
			
		}
		
        conn.close();
        statement.close();
        result.close();
		return 0;
		
	}
	
	/**
	 * Allows a student to join a team.
	 * 
	 * @param labName
	 * @param teamName
	 * @return true if successfully joins team, false if not
	 * @throws SQLException
	 */
	public Boolean joinTeam(String labName, String teamName) throws SQLException {
		
		conn = connect();
		query = "SELECT * FROM LABS WHERE LABNAME ='" + labName + "'";
    	PreparedStatement statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
           
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		result.next();
		int teamSize = result.getInt("TEAMSIZE");		
		
		query = "SELECT * FROM " + labName + "' WHERE TEAM ='" + teamName + "'";
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		
		int studentCount = 0;
		while(result.next()) {
			studentCount++;
		}
		
		if (studentCount < teamSize) {
			
			query = "UPDATE " + labName + " SET TEAM ='" + teamName + "' WHERE STUDENT ='" + getUserName() + "'";
			statement = conn.prepareStatement(query);
	        try {
				result = statement.executeQuery();
			} catch (SQLException e) {
	            
				System.out.println(e.toString());
				
			}
	       
			return true;
			
		}
		
		query = "SELECT * FROM " + labName + "' WHERE TEAM IS NOT NULL";
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		int labCount = 0;
		while(result.next()) {
			labCount++;
		}
		
		query = "SELECT * FROM LABS WHERE LABNAME ='" + labName + "'";
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		result.next();
		String courseName = result.getString("COURSE");
		
		query = "SELECT * FROM " + courseName + "'";
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		int totalStudents = 0;
		while(result.next()) {
			totalStudents++;
		}
		
		if (studentCount == teamSize && (totalStudents - labCount) < teamSize) {
			
			query = "UPDATE " + labName + " SET TEAM ='" + teamName + "' WHERE STUDENT ='" + getUserName() + "'";
			statement = conn.prepareStatement(query);
	        try {
				result = statement.executeQuery();
			} catch (SQLException e) {
	            
				System.out.println(e.toString());
				logger.error(e.toString());
			}
	        conn.close();
	        statement.close();
	        result.close();
			return true;
			
		}
		
        conn.close();
        statement.close();
        result.close();
		return false;
		
	}
	
}
